﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMSX_get_server_list]
#-- Purpose:		Get a list of managed servers to query for updated information
#--	Last Update:	04/28/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMSX_get_server_list]
(
	@only_visible_servers		bit = 0
)
AS

SET NOCOUNT ON

SELECT		sql_type_ro,
			sql_description, 
			sql_server_name, 
			sql_product_version,
			CAST(CASE WHEN should_hide = 1 THEN 0 ELSE access_granted END as bit) is_visible
FROM		(
			SELECT		ssd.ssd_type_ro sql_type_ro,
						app.[app_name] sql_description,
						ssd.ssd_sqlserver_name_ro sql_server_name, 
						sql.sql_product_version,
						CAST(MAX(CAST(ISNULL(rst.access_granted, 0) as int)) as bit) access_granted,
						CAST(MAX(CAST(ISNULL(rst.rst_should_hide, 0) as int)) as bit) should_hide
			FROM		[dbo].[tblMSX_server_sql_info] sql
			JOIN		[dbo].[tblMSX_server_discovery] ssd ON ssd.ssd_id = sql.sql_ssd_id
			LEFT JOIN	[dbo].[tblMSX_application] app ON ssd.[ssd_app_id] = app.[app_id]
			LEFT JOIN	(
						SELECT		rst_server_name,
									(CASE	WHEN IS_MEMBER(rst_username) = 1 OR rst_username = '*'
											THEN 1 ELSE 0 END) access_granted,
									(CASE	WHEN rst_username = SUSER_NAME() 
											THEN rst_should_hide ELSE 0 END) rst_should_hide
						FROM		[dbo].[tblMSX_server_restore_user]
						) rst ON rst.rst_server_name = ssd.ssd_sqlserver_name_ro
							OR rst.rst_server_name = '*'
			WHERE		ssd.ssd_is_disabled = 0
						AND ssd.ssd_is_vendor_managed_ro = 0
			GROUP BY	ssd.ssd_type_ro,
						app.[app_name],
						ssd.ssd_sqlserver_name_ro, 
						sql.sql_product_version
			) t1
WHERE		(
			ISNULL(@only_visible_servers, 0) = 0
			OR CAST(CASE WHEN should_hide = 1 THEN 0 ELSE access_granted END as bit) = 1
			)
ORDER BY	sql_type_ro,
			sql_server_name

SET NOCOUNT OFF